IF OBJECT_ID('dbo.vwInfoEdCSCRDeliverablesCompleted') IS NOT NULL
BEGIN
    DROP VIEW dbo.vwInfoEdCSCRDeliverablesCompleted
    IF OBJECT_ID('dbo.vwInfoEdCSCRDeliverablesCompleted') IS NOT NULL
        PRINT '<<< FAILED DROPPING VIEW dbo.vwInfoEdCSCRDeliverablesCompleted >>>'
    ELSE
        PRINT '<<< DROPPED VIEW dbo.vwInfoEdCSCRDeliverablesCompleted >>>'
END
go
-- =======================================================================
-- Object Name: (VIEW) dbo.vwInfoEdCSCRDeliverablesCompleted
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 10/9/2007 
--
-- Description: View to Completed Deliverables, Calculating days it took
--        to Complete.
--
-- Used BY Procedure/Report:  dbo.RptCSCRActivityReport
--
-- ** Requires Function dbo.fnRemoveTime() **
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 10/9/07     KC        Created
-- 10/10/07    KC        Added Dates back to Output, so can calculate
--                       days diff another way if need be.
-- 03/25/08    KC        Converted to ProcessedDate logic.
--
-- =======================================================================
CREATE VIEW dbo.vwInfoEdCSCRDeliverablesCompleted
AS
      
SELECT    COMPL.prop_no,
          COMPL.Reportid,
          COMPL.REPORTCAT,
          CREAT.CreatedDate,
          COMPL.COMPLETEDDATE,
          DATEDIFF(dd,CREAT.CreatedDate,COMPL.COMPLETEDDATE) AS DaysDiff
FROM      (          
          -- Completed Deliverables
          SELECT    prop_no,
                    ReportId,
                    REPORTCAT,
                    CASE WHEN ConvertedInd = 1 AND MAX(CompletedDate) IS NOT NULL THEN dbo.fnRemoveTime(MAX(CompletedDate))
                         ELSE dbo.fnRemoveTime(MAX(StatusDate))
                    END AS COMPLETEDDATE
          FROM      (SELECT      D.prop_no,
                                 D.ReportId AS ReportId,
                                 D.REPORTCAT AS REPORTCAT,
                                 D.CompletedDate,
                                 CASE WHEN S.REPORTSTAT = 'PARTNERS_P_REPORTSTAT_61' AND D.CompletedDate IS NOT NULL
                                      -- Convert to CSCR - Completed
                                      THEN 'CSCR - Completed'
                                      ELSE RTRIM(C3.code_desc)
                                      END AS REPORTSTAT,
                                 S.REPORTSTAT AS REPORTSTAT_CODE,
                                 CASE WHEN dbo.fnRemoveTime(S.ProcessedDate) IN ('8/8/2007','8/12/2007')
                                      THEN dbo.fnRemoveTime(S.StatusDate)
                                      ELSE dbo.fnRemoveTime(S.ProcessedDate)
                                 END AS StatusDate,
                                 CASE WHEN D.SubmittedDate IS NOT NULL
                                      THEN 1
                                      ELSE 0
                                 END AS ConvertedInd
                       FROM      dbo.InfoEdPTDeliverable D LEFT OUTER JOIN dbo.InfoEdCodetab C1 ON D.REPORTCAT = C1.codeID
                                      LEFT OUTER JOIN dbo.InfoEdCodetab C2 ON D.REPORTTYPE = C2.codeID
                                      LEFT OUTER JOIN dbo.InfoEdPTDeliverableStatus S ON D.DeliverableID = S.DeliverableID
                                      LEFT OUTER JOIN dbo.InfoEdCodetab C3 ON S.REPORTSTAT = C3.codeID
                       WHERE     D.Inst_Code = 'PARTNERS' AND 
                                 D.REPORTCAT IN ('PARTNERS_P_REPORTCAT_18','PARTNERS_P_REPORTCAT_19',
                                                'PARTNERS_P_REPORTCAT_30','PARTNERS_P_REPORTCAT_33',
                                                'PARTNERS_P_REPORTCAT_34','PARTNERS_P_REPORTCAT_36')) DEL

          WHERE      DEL.REPORTSTAT LIKE '%Completed%'
          GROUP BY   DEL.prop_no,
                     DEL.ReportId,
                     DEL.REPORTCAT,
                     DEL.ConvertedInd ) COMPL 
          INNER JOIN (   SELECT    D.prop_no,
                                   D.reportid,
                                   D.REPORTCAT,
                                   -- Lots of converted records we need to ignore. almost 38K, and over 13K respectively
                                   -- for these dates. 
                                   MIN( CASE WHEN dbo.fnRemoveTime(S.ProcessedDate) IN ('8/8/2007','8/12/2007')
                                        THEN  dbo.fnRemoveTime(ISNULL(D.StatusDate,S.StatusDate))
                                        ELSE  dbo.fnRemoveTime(S.ProcessedDate)
                                   END) AS CreatedDate
                         FROM      dbo.InfoEdPTDeliverable D INNER JOIN dbo.InfoEdPTDeliverableStatus S ON D.DeliverableID = S.DeliverableID
                         GROUP BY  D.prop_no,
                                   D.reportid,
                                   D.REPORTCAT ) CREAT ON COMPL.prop_no = CREAT.prop_no 
           AND COMPL.ReportId = CREAT.ReportId AND COMPL.REPORTCAT = CREAT.REPORTCAT

go
IF OBJECT_ID('dbo.vwInfoEdCSCRDeliverablesCompleted') IS NOT NULL
    PRINT '<<< CREATED VIEW dbo.vwInfoEdCSCRDeliverablesCompleted >>>'
ELSE
    PRINT '<<< FAILED CREATING VIEW dbo.vwInfoEdCSCRDeliverablesCompleted >>>'
go
